package luke4fun.wheresmymoney.db;

import android.provider.BaseColumns;

public class DBConstants implements BaseColumns {

	public static final String TABLE_RECORD = "record";
	public static final String TABLE_CATEGORY = "category";
	public static final String TABLE_ITEM = "item";
	public static final String TABLE_STORE = "store";
	public static final String TABLE_BUDGET = "budget";
	public static final String TABLE_BUDGET_DETAIL = "budget_detail";
	
	public static String T_CREATE_RECORD;
	public static String T_CREATE_CATEGORY;
	public static String T_CREATE_ITEM;
	public static String T_CREATE_STORE;
	public static String T_CREATE_BUDGET;
	public static String T_CREATE_BUDGET_DETAIL;
	
	public static String V_RECORD = "view_record";
	public static String V_CREATE_RECORD;
	public static String V_DROP_RECORD;
	public static String V_MONTH_RECORD = "view_month_record";
	public static String V_CREATE_MONTH_RECORD;
	public static String V_BUDGET = "view_budget";
	public static String V_CREATE_BUDGET;
	
	public static final String RECORD_ID = "_id";
	public static final String RECORD_PID = "pid";
	public static final String RECORD_PRICE = "price";
	public static final String RECORD_ITEM = "item";
	public static final String RECORD_DATATIME = "datatime";
	public static final String RECORD_CATEGORY = "category";
	public static final String RECORD_STORE = "store";
	public static final String RECORD_DESC = "desc";
	public static final String RECORD_IMG = "img";
	
	public static final String CATEGORY_ID = "_id";
	public static final String CATEGORY_NAME = "name";
	public static final String CATEGORY_COLOR = "color";
	public static final String CATEGORY_FREQUENCE = "frequence";
	
	public static final String ITEM_ID = "_id";
	public static final String ITEM_NAME = "name";
	
	public static final String STORE_ID = "_id";
	public static final String STORE_NAME = "name";
	
	public static final String BUDGET_ID = "_id";
	public static final String BUDGET_FROM = "budget_from";
	public static final String BUDGET_TO = "budget_to";
	public static final String BUDGET_BUDGET = "budget";
	
	public static final String BUDGET_DETAIL_ID = "_id";
	public static final String BUDGET_DETAIL_PID = "pid";
	public static final String BUDGET_DETAIL_CATEGORY = "category";
	public static final String BUDGET_DETAIL_BUDGET = "budget";
	
	public static final String COLUMN_SUM = "summarize";
	public static final String COLUMN_PERCENT = "percent";
	
	public static final String QUERY_MONTH_SUMMARY = sqlForMonthSummary();
	static {
		initRecordSql();
		initCategorySql();
		initItemSql();
		initStoreSql();
		initBudgetSql();
		initMonthRecordViewSql();
		initBudgetViewSql();
	}
	
	private static void initRecordSql() {
		StringBuffer sb = new StringBuffer("CREATE TABLE ");
		sb.append(TABLE_RECORD).append(" (");
		sb.append(RECORD_ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ");
		sb.append(RECORD_PID).append(" INTEGER, ");
		sb.append(RECORD_DATATIME).append(" INTEGER, ");
		sb.append(RECORD_PRICE).append(" INTEGER, ");
		sb.append(RECORD_ITEM).append(" INTEGER, ");
		sb.append(RECORD_CATEGORY).append(" INTEGER, ");
		sb.append(RECORD_STORE).append(" INTEGER, ");
		sb.append(RECORD_DESC).append(" TEXT, ");
		sb.append(RECORD_IMG).append(" TEXT)");
		
		T_CREATE_RECORD = sb.toString();
	}
	
	private static void initCategorySql() {
		StringBuffer sb = new StringBuffer("CREATE TABLE ");
		sb.append(TABLE_CATEGORY).append(" (")
		  .append(CATEGORY_ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
		  .append(CATEGORY_NAME).append(" TEXT, ")
		  .append(CATEGORY_COLOR).append(" INTEGER, ")
		  .append(CATEGORY_FREQUENCE).append(" INTEGER)");
		
		T_CREATE_CATEGORY = sb.toString();
	}
	
	private static void initItemSql() {
		StringBuffer sb = new StringBuffer("CREATE TABLE ");
		sb.append(TABLE_ITEM).append(" (");
		sb.append(ITEM_ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ");
		sb.append(ITEM_NAME).append(" TEXT)");
		
		T_CREATE_ITEM = sb.toString();
	}
	
	private static void initStoreSql() {
		StringBuffer sb = new StringBuffer("CREATE TABLE ");
		sb.append(TABLE_STORE).append(" (");
		sb.append(STORE_ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ");
		sb.append(STORE_NAME).append(" TEXT)");
		
		T_CREATE_STORE = sb.toString();
	}
	
	private static void initBudgetSql() {
		StringBuffer sb = new StringBuffer("CREATE TABLE ");
		sb.append(TABLE_BUDGET).append(" (")
		  .append(BUDGET_ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
		  .append(BUDGET_FROM).append(" INTEGER, ")
		  .append(BUDGET_TO).append(" INTEGER, ")
		  .append(BUDGET_BUDGET).append(" INTEGER);");
		
		T_CREATE_BUDGET = sb.toString();
		
		sb = new StringBuffer("CREATE TABLE ");
		sb.append(TABLE_BUDGET_DETAIL).append(" (")
		  .append(BUDGET_DETAIL_ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
		  .append(BUDGET_DETAIL_PID).append(" INTEGER, ")
		  .append(BUDGET_DETAIL_CATEGORY).append(" INTEGER, ")
		  .append(BUDGET_DETAIL_BUDGET).append(" INTEGER);");
		
		T_CREATE_BUDGET_DETAIL = sb.toString();
		
	}
	
	private static void initMonthRecordViewSql() {
		StringBuffer sb = new StringBuffer("CREATE VIEW IF NOT EXISTS ").append(V_RECORD).append(" AS");
		sb.append(" SELECT record._ID as _id, record.pid as pid, record.datatime as datatime, record.price as price, ");
		sb.append(" (SELECT name FROM " + TABLE_ITEM + " WHERE " + ITEM_ID + "=record." + RECORD_ITEM + ") as item,");
		sb.append(" (SELECT name FROM " + TABLE_CATEGORY + " WHERE " + CATEGORY_ID + "=record." + RECORD_CATEGORY + ") as category, ");
		sb.append(" (SELECT name FROM " + TABLE_STORE + " WHERE " + STORE_ID + "=record." + RECORD_STORE + ") as store, ");
		sb.append(" record.desc, record.img");
		sb.append(" FROM ");
		sb.append(DBConstants.TABLE_RECORD).append(" record");
		
		V_CREATE_RECORD = sb.toString();
		V_DROP_RECORD = "DROP VIEW IF EXISTS " + V_RECORD;
	}
	
	private static void initBudgetViewSql() {
		StringBuilder sb = new StringBuilder();
		sb.append("CREATE VIEW IF NOT EXISTS ").append(V_BUDGET).append(" AS")
//		  .append(" SELECT budget._ID as budget_id, budget.budget_from as budget_budget_from,")
//		  .append(" budget.budget_to as budget_budget_to, budget.budget as budget_budget,")
//		  .append(" budget_detail._ID as budget_detail_id, budget_detail.pid as budget_detail_pid")
//		  .append(" budget_detail.category as budget_detail_category, budget_detail.budget as budget_detail_budget,")
//		  .append(" category._id as category_id, category.name as category_name,")
//		  .append(" category.color as category_color, category.frequence as category_frequence")
		  .append(" SELECT *, (SELECT SUM(price) as expense FROM record WHERE category = category._id and datatime >= budget.budget_from and datatime < budget.budget_to)")
		  .append(" FROM ")
		  .append(TABLE_BUDGET)
		  .append(" LEFT JOIN ")
		  .append(TABLE_BUDGET_DETAIL)
		  .append(" ON budget._id=budget_detail.pid")
		  .append(" LEFT JOIN ")
		  .append(TABLE_CATEGORY)
		  .append(" ON budget_detail.category=category._id")
		  .append(" ORDER BY budget._id DESC, budget_detail.budget");
		
		V_CREATE_BUDGET = sb.toString();
	}
	
	private static String sqlForMonthSummary() {
		StringBuffer sb = new StringBuffer();
		
		// select (select name from catetory where _id=t1.category) as category,
		// (select color from category where _id=t1.category) as color,
		// t1.sum as sum, (t1.sum/t2.sum) as percentage
		// from
		// (select record.category as catetory_id, sum(price) as sum from record group by category having datatime >= ? and datatime < ?) t1
		// (select sum(price) as sum from record where datatime >= ? and datatime < ?) t2
		sb.append("SELECT (");
		sb.append(" SELECT ").append(CATEGORY_NAME);
		sb.append(" FROM ").append(TABLE_CATEGORY);
		sb.append(" WHERE ").append(CATEGORY_ID).append("=t1.category_id");
		sb.append(" ) as ").append(CATEGORY_NAME);
		sb.append(", (SELECT ").append(CATEGORY_COLOR);
		sb.append(" FROM ").append(TABLE_CATEGORY);
		sb.append(" WHERE ").append(CATEGORY_ID).append("=t1.category_id");
		sb.append(" ) as ").append(CATEGORY_COLOR);
		sb.append(", t1.sum as ").append(COLUMN_SUM);
		sb.append(", (100.0*t1.sum/t2.sum) as ").append(COLUMN_PERCENT);
		
		sb.append(" FROM (");
		
		sb.append(" SELECT ").append(RECORD_CATEGORY).append(" as category_id,");
		sb.append(" SUM(price) as sum");
		sb.append(" FROM ").append(TABLE_RECORD);
		sb.append(" WHERE ").append(RECORD_DATATIME).append(" >= ? ");
		sb.append(" AND ").append(RECORD_DATATIME).append(" < ?");
		sb.append(" GROUP BY ").append(RECORD_CATEGORY);
		sb.append(" ) t1,");
		sb.append(" (");
		sb.append(" SELECT SUM(price) as sum");
		sb.append(" FROM ").append(TABLE_RECORD);
		sb.append(" WHERE ").append(RECORD_DATATIME).append(" >= ?");
		sb.append(" AND ").append(RECORD_DATATIME).append(" < ?");
		sb.append(" ) t2");
		
		sb.append(" ORDER BY ").append(COLUMN_PERCENT);
		
		return sb.toString();
	}
}
